
/*
'===============================================================================
'  HNG.Portal.BL.TblDanhMucSanPhamManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNG.Portal.BLL;
using HNG.Portal.DAL;
using HNG.Portal.DAL.EntityClasses;
using HNG.Portal.DAL.FactoryClasses;
using HNG.Portal.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using System.Text;

namespace HNG.Portal.BLL
{
    public class TblDanhMucSanPhamManager : TblDanhMucSanPhamManagerBase
    {
        /// <summary>
        /// Purpose: Class constructor.
        /// </summary>
        public TblDanhMucSanPhamManager()
        {
            // Nothing for now.
        }
        public DataTable Get_Bykeysearch(string keysearch, string columnName, int LangID)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            string strQuery = "select * from TblDanhMucSanPham ";
            if (columnName != "")
                strQuery += " where " + columnName + " like N'%" + keysearch + "%'" + " and LangID= " + LangID + " Order by ID desc";
            else
                strQuery += " where TenDanhMuc like N'%" + keysearch + "%'" + " and LangID= " + LangID + " Order by ID desc";
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, strQuery);
        }

        public DataTable SelectCommon(TblDanhMucSanPhamEntity _TblDanhMucSanPhamEntity)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.SortExpression) ? "[ID]" : _TblDanhMucSanPhamEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.SortDirection) ? " DESC" : _TblDanhMucSanPhamEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[LangID],").Append("\n");
                sbSql.Append("  ch.[DanhMucCha],").Append("\n");
                sbSql.Append("  ch.[TenDanhMucCha],").Append("\n");
                sbSql.Append("  ch.[TenDanhMuc],").Append("\n");
                sbSql.Append("  ch.[ThuTu],").Append("\n");
                sbSql.Append("  ch.[ViTri],").Append("\n");
                sbSql.Append("  ch.[MoTa],").Append("\n");
                sbSql.Append("  ch.[Url],").Append("\n");
                sbSql.Append("  ch.[Icon],").Append("\n");
                sbSql.Append("  ch.[Type],").Append("\n");
                sbSql.Append("  ch.[TrangThai],").Append("\n");
                sbSql.Append("  lng.[Name] LangName").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  TblDanhMucSanPham ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                sbSql.Append("  ON lng.ID = ch.LangID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.ID))
                {
                    sbSql.AppendFormat("  AND ch.[ID] LIKE {0}", _TblDanhMucSanPhamEntity.Id).Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[ID] LIKE N'%' + {0} + '%'", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE N'%' + {0} + '%'", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DanhMucCha] LIKE N'%' + {0} + '%'", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenDanhMuc] LIKE N'%' + {0} + '%'", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[MoTa] LIKE N'%' + {0} + '%'", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE N'%' + {0} + '%'", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Icon] LIKE N'%' + {0} + '%')", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Type] LIKE N'%' + {0} + '%')", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[IsHome] LIKE N'%' + {0} + '%'", _TblDanhMucSanPhamEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TrangThai] LIKE N'%' + {0} + '%')", _TblDanhMucSanPhamEntity.Condition).Append("\n");

                }

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.LangId))
                {
                    sbSql.AppendFormat("  AND ch.[LangId] Like '{0}'", _TblDanhMucSanPhamEntity.LangId).Append("\n");
                }

                if (_TblDanhMucSanPhamEntity.DanhMucCha != null)
                {
                    sbSql.AppendFormat("  AND ch.[DanhMucCha] Like {0}", _TblDanhMucSanPhamEntity.DanhMucCha).Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.Type))
                {
                    sbSql.AppendFormat("  AND ch.[type] Like '{0}'", _TblDanhMucSanPhamEntity.Type).Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.IsHome.ToString()))
                {
                    sbSql.AppendFormat("  AND ch.[IsHome] Like {0}", _TblDanhMucSanPhamEntity.IsHome).Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.TrangThai.ToString()))
                {
                    sbSql.AppendFormat("  AND ch.[TrangThai] Like {0}", _TblDanhMucSanPhamEntity.TrangThai).Append("\n");
                }
                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblDanhMucSanPhamEntity.StartIndex, _TblDanhMucSanPhamEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString());
        }

        public DataTable be_SelectToDataList(TblDanhMucSanPhamEntity _TblDanhMucSanPhamEntity)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            StringBuilder sbSql = new StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[2];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.SortExpression) ? "[ID]" : _TblDanhMucSanPhamEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.SortDirection) ? " DESC" : _TblDanhMucSanPhamEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TenDanhMucCha],").Append("\n");
                sbSql.Append("  ch.[TenDanhMuc],").Append("\n");
                sbSql.Append("  ch.[ThuTu],").Append("\n");
                sbSql.Append("  ch.[Url],").Append("\n");
                sbSql.Append("  ch.[Icon],").Append("\n");
                sbSql.Append("  ch.[ViTri],").Append("\n");
                sbSql.Append("  ch.[IsHome],").Append("\n");
                sbSql.Append("  ch.[TrangThai]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblDanhMucSanPham ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                sbSql.Append("  ON lng.ID = ch.LangID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblDanhMucSanPhamEntity.Id);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");
                }
                string strCon = _TblDanhMucSanPhamEntity.Condition;
                if (!string.IsNullOrEmpty(strCon))
                {
                    sbSql.AppendFormat("  AND (ch.[ID] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenDanhMucCha] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenDanhMuc] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[MoTa] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Icon] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Type] LIKE '%{0}%'", strCon).Append("\n");

                    sbSql.AppendFormat("  OR ch.[ID] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenDanhMucCha] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenDanhMuc] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[MoTa] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Icon] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Type] LIKE N'%{0}%'", strCon).Append("\n");

                    sbSql.Append(")").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblDanhMucSanPhamEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] Like @LangId").Append("\n");
                }

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblDanhMucSanPhamEntity.StartIndex, _TblDanhMucSanPhamEntity.EndIndex).Append("\n");
            }
            catch { }


            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public DataTable be_SelectToDropdownlist(TblDanhMucSanPhamEntity _TblDanhMucSanPhamEntity)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            StringBuilder sbSql = new StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[2];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.SortExpression) ? "[ID]" : _TblDanhMucSanPhamEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.SortDirection) ? " DESC" : _TblDanhMucSanPhamEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[DanhMucCha],").Append("\n");
                sbSql.Append("  ch.[TenDanhMuc]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblDanhMucSanPham ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                sbSql.Append("  ON lng.ID = ch.LangID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblDanhMucSanPhamEntity.Id);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");
                }
                if (!string.IsNullOrEmpty(_TblDanhMucSanPhamEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblDanhMucSanPhamEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] Like @LangId").Append("\n");
                }

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblDanhMucSanPhamEntity.StartIndex, _TblDanhMucSanPhamEntity.EndIndex).Append("\n");
            }
            catch { }


            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public int be_UpdateOrderUp(int CateID)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@ID", CateID);
            return AE.SqlHelper.ExecuteNonQuery(Conn, "sp_CateProductOrdersUp", _SqlParameter);
        }

        public int be_UpdateOrderDown(int CateID)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@ID", CateID);
            return AE.SqlHelper.ExecuteNonQuery(Conn, "sp_CateProductOrdersDown", _SqlParameter);
        }
    }
}